package com.interface21.samples.countries.web.views;

import java.util.Iterator;
import java.util.Locale;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.interface21.beans.BeanWrapper;
import com.interface21.beans.BeanWrapperImpl;
import com.interface21.beans.SortDefinition;
import com.interface21.samples.countries.appli.ICountry;
import com.interface21.util.RefreshablePagedListHolder;
import com.interface21.web.servlet.view.document.AbstractExcelView;

/**
 * This view demonstrates how to send an Excel file with the Spring Framework
 * using the jakarta's POI library.
 * <br>Here create a document from scratch, but it is also possible to start from a template
 * document. In this case, add an url property in the view definition like:
 * <br>countries_excelView.url=/WEB-INF/views/excel/countries
 * <br>Creating the directories, put an excel file '/WEB-INF/views/excel/countries.xls', it will be taken
 * as a starting point.
 * <br>You can also add in the same directory files like 'countries_en.xls', 'countries_fr.xls' and so on.
 * Theses files will take precedence if the user's locale matches.
 *
 * @author Jean-Pierre Pawlak
 */
public class CountriesExcelView extends AbstractExcelView {

    /**
     * @see com.interface21.web.servlet.view.document.AbstractExcelView#buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
     */
    protected void buildExcelDocument(
            Map model,
            HSSFWorkbook wb,
            HttpServletRequest request,
            HttpServletResponse response) {

        HSSFSheet sheet;
        HSSFCell cell;
        int row = 0;

        // We search the data to insert
        RefreshablePagedListHolder pgHolder = (RefreshablePagedListHolder) model.get("countries");
        Locale loc = pgHolder.getLocale();

        // As we use a from scratch document, we create a new sheet
        sheet = wb.createSheet("SPRING-Countries");
        // If we will use the first sheet from an existing document, replace by this:
        // sheet = wb.getSheetAt(0);

        // We simply put an error message on the first cell if no list is available
        // Nevertheless, it should never be null as the controller verify it.
        if (null == pgHolder) {
            getCell(sheet, 0, 0).setCellValue(getMessage("nolist", loc));
            return;
        }

        // We create a font for headers
        HSSFFont f = wb.createFont();
        // set font 1 to 12 point type
        f.setFontHeightInPoints((short) 12);
        // make it blue
        f.setColor((short) 0xc);
        // make it bold arial is the default font
        f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // We create a style for headers
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setFont(f);
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // The same for properties data
        HSSFFont fp = wb.createFont();
        fp.setColor((short) 0xc);
        HSSFCellStyle csp = wb.createCellStyle();
        csp.setFont(fp);
        csp.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // We create a date style
        HSSFCellStyle dateStyle = wb.createCellStyle();
        dateStyle.setFont(fp);
        dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

        // We set the colum width of the two first columns
        sheet.setColumnWidth((short) 0, (short) (20 * 256));
        sheet.setColumnWidth((short) 1, (short) (20 * 256));

        // We prepare some data
        SortDefinition sort = pgHolder.getSort();
        BeanWrapper bw = new BeanWrapperImpl(pgHolder.getFilter());
        String filterName = (String) bw.getPropertyValue("name");
        String filterCode = (String) bw.getPropertyValue("code");

        // We put some information about the user request on the sheet
        // getCell is a useful add-on provided by the AbstractExcelView
        // The labels could be pre-inserted in a template document
        getCell(sheet, row, 0).setCellValue(getMessage("date.extraction", loc));
        getCell(sheet, row, 1).setCellValue(pgHolder.getRefreshDate());
        getCell(sheet, row, 1).setCellStyle(dateStyle);
        row++;

        getCell(sheet, row, 0).setCellValue(getMessage("nbRecords", loc));
        getCell(sheet, row, 1).setCellValue(pgHolder.getNrOfElements());
        getCell(sheet, row, 1).setCellStyle(csp);
        row++;

        getCell(sheet, row, 0).setCellValue(getMessage("sort.name", loc));
        getCell(sheet, row, 1).setCellValue(getMessage(sort.getProperty(), loc));
        getCell(sheet, row, 1).setCellStyle(csp);
        row++;

        getCell(sheet, row, 0).setCellValue(getMessage("sort.asc", loc));
        getCell(sheet, row, 1).setCellValue(getMessage(Boolean.toString(sort.isAscending()), loc));
        getCell(sheet, row, 1).setCellStyle(csp);
        row++;

        getCell(sheet, row, 0).setCellValue(getMessage("sort.igncase", loc));
        getCell(sheet, row, 1).setCellValue(getMessage(Boolean.toString(sort.isIgnoreCase()), loc));
        getCell(sheet, row, 1).setCellStyle(csp);
        row++;

        getCell(sheet, row, 0).setCellValue(getMessage("filter.name", loc));
        getCell(sheet, row, 1).setCellValue(filterName);
        getCell(sheet, row, 1).setCellStyle(csp);
        row++;

        getCell(sheet, row, 0).setCellValue(getMessage("filter.code", loc));
        getCell(sheet, row, 1).setCellValue(filterCode);
        getCell(sheet, row, 1).setCellStyle(csp);
        // row += 3;

        // We create a second shhet for the data
        sheet = wb.createSheet(getMessage("countries", loc));
        sheet.setColumnWidth((short) 1, (short) (30 * 256));
        row = 0;

        // We put now the headers of the list on the sheet
        cell = getCell(sheet, row, 0);
        cell.setCellStyle(cs);
        cell.setCellValue(getMessage("code", loc));
        cell = getCell(sheet, row, 1);
        cell.setCellStyle(cs);
        cell.setCellValue(getMessage("name", loc));
        row++;

        // We put now the countries from the list on the sheet
        Iterator it = pgHolder.getSource().iterator();
        while (it.hasNext()) {
            ICountry country = (ICountry) it.next();
            getCell(sheet, row, 0).setCellValue(country.getCode());
            getCell(sheet, row, 1).setCellValue(country.getName());
            row++;
        }
    }

    private String getMessage(String key, Locale locale) {
        return this.getApplicationContext().getMessage(key, null, key, locale);
    }

}
